package com.realtyManage.springboot.Dao;


import com.realtyManage.springboot.Model.Do.User;
import com.realtyManage.springboot.Model.Do.UserTask;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.util.Date;
import java.util.List;

/**
 * 用户服务数据接口类
 *
 * @author 杭州素色网络科技有限公司
 * @since 2019-07-21
 */

@Repository
public interface UserTaskRepository extends JpaRepository<UserTask, Integer>, JpaSpecificationExecutor<UserTask> {

    @Modifying
    @Transactional
    @Query("update UserTask a set " +
            "a.taskType = CASE WHEN :#{#userTask.taskType} IS NULL THEN a.taskType ELSE :#{#userTask.taskType} END ," +
            "a.userId = CASE WHEN :#{#userTask.userId} IS NULL THEN a.userId ELSE :#{#userTask.userId} END ," +
            "a.distributionUserId = CASE WHEN :#{#userTask.distributionUserId} IS NULL THEN a.distributionUserId ELSE :#{#userTask.distributionUserId} END ," +
            "a.taskDetail = CASE WHEN :#{#userTask.taskDetail} IS NULL THEN a.taskDetail ELSE :#{#userTask.taskDetail} END ," +
            "a.startTime = CASE WHEN :#{#userTask.startTime} IS NULL THEN a.startTime ELSE :#{#userTask.startTime} END ," +
            "a.endTime = CASE WHEN :#{#userTask.endTime} IS NULL THEN a.endTime ELSE :#{#userTask.endTime} END ," +
            "a.approval = CASE WHEN :#{#userTask.approval} IS NULL THEN a.approval ELSE :#{#userTask.approval} END ," +
            "a.completeDescription = CASE WHEN :#{#userTask.completeDescription} IS NULL THEN a.completeDescription ELSE :#{#userTask.completeDescription} END ," +
            "a.completeVoucher = CASE WHEN :#{#userTask.completeVoucher} IS NULL THEN a.completeVoucher ELSE :#{#userTask.completeVoucher} END " +
            "where a.id = :#{#userTask.id}")
    int update(@Param("userTask") UserTask userTask);


    @Modifying
    @Query(value = "UPDATE user_task SET is_show = 1 WHERE id in (?1)",nativeQuery=true)
    UserTask deleteTask(String ids);

    @Query(value = "select * from  user_task WHERE user_id LIKE %?1% and task_type = 0 AND end_time LIKE %?2%",nativeQuery=true)
    List<UserTask> queryMyTask(Integer userId,String date);

    @Query(value = "select * from  user_task WHERE user_id LIKE %?1% and task_type = 1 AND end_time LIKE %?2%",nativeQuery=true)
    List<UserTask> queryFMyTask(Integer userId,String date);

    @Query(value = "select COUNT(1) num from customer_registration WHERE reception_id IN (?1) AND come_time LIKE %?2% AND visit = 1\n" +
            "UNION ALL \n" +
            "SELECT COUNT(1) tzNum FROM customer_registration WHERE reception_id IN (?1) AND come_time LIKE %?2% AND expand_customer = 1  \n" +
            "UNION ALL  \n" +
            "SELECT COUNT(1) yyNum FROM customer_registration WHERE reception_id IN (?1) AND come_time LIKE %?2% AND invitation = 1 \n" +
            "UNION ALL  \n" +
            "SELECT COUNT(1) num from (SELECT COUNT(DISTINCT(custom_id)) oldS from customer_record WHERE info_time LIKE %?2% AND uid IN (?1) group by custom_id having count(custom_id) >=2) a\n" +
            "UNION ALL  \n" +
            "SELECT COUNT(1) rcNum FROM customer_registration WHERE reception_id IN (?1) AND come_time LIKE %?2% AND identify = 1  \n" +
            "UNION ALL  \n" +
            "SELECT COUNT(1) cjNum FROM customer_registration WHERE reception_id IN (?1) AND come_time LIKE %?2% AND customer_type NOT IN('小订','继续跟踪','认筹','明确拒绝','已退房') \n" +
            "UNION ALL  \n" +
            "SELECT COUNT(1) hkNum FROM customer_registration WHERE reception_id IN (?1) AND come_time LIKE %?2% AND money_back != 0  \n" +
            "UNION ALL  \n" +
            "SELECT COUNT(1) zfNum FROM forward WHERE user_id IN (?1) AND forward_date LIKE %?2%",nativeQuery=true)
    List<String> completeMyTaskDetail(List ids,String date);

    @Query(value = "SELECT * FROM user_task WHERE task_type = 1 AND complete_voucher IS NOT NULL",nativeQuery=true)
    List<UserTask> getInformal();

    @Modifying
    @Transactional
    @Query(value = "delete from user_task where id = ?1",nativeQuery = true)
    int deleteUserTaskById (String id);


    @Query(value = "SELECT count(1) FROM user_task WHERE end_time = ?1",nativeQuery=true)
    int yz(String time);

    @Query(value = "select * from  user_task WHERE id = ?1",nativeQuery=true)
    UserTask queryTaskDetailById(Integer userId);

}
